-- @owner: opentestcase003
-- @date: 2022-07-08
-- @testpoint: covar_pop函数在case when语句中使用

--step1: 创建表；expect:成功
drop table if exists t_ustore_function_covarpop_0004 cascade;
create table  t_ustore_function_covarpop_0004(col_1 int,col_2 integer, col_3 bigint,col_4 real,col_5 float8,col_6 float,col_7 decimal(12,6),col_8 number,col_9 numeric,col_10 char(30),col_11 varchar(30),col_12 varchar2(30),col_13 blob,col_14 date,col_15 date,col_16 timestamp,col_17 timestamp with time zone,col_18 timestamp with time zone,col_19 bool,col_20 boolean,col_21 interval year to month,col_22 interval day to second,col_23 blob,col_24 clob,col_25 int[]) with (storage_type=ustore);
truncate table t_ustore_function_covarpop_0004;

--step2: 创建序列；expect:成功
drop sequence if exists s_ustore_function_covarpop_0004;
create sequence s_ustore_function_covarpop_0004 increment by 1 start with 10;

--step3: 插入数据；expect:成功
begin
 for i in 1..50 loop
      insert into t_ustore_function_covarpop_0004 values(i,i+1,s_ustore_function_covarpop_0004.nextval,i+3.1415926,i+445.255,3.1415926-i*2,98*0.99*i,
      99*1.01*i,-98*0.99*i,lpad('abc','30','@')::varchar,lpad('abc','30','b')::varchar2,rpad('abc','30','e')::blob,
      '1111111111','2019-01-03 15:19:00','2019-01-03 15:19:00',
      to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
      to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
      to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
      true,false,(interval '99-02' year to month),(interval '4 5:12:10.222' day to second(3)),
      lpad('10','12','01010')::blob,rpad('abc','9','a@123&^%djgk')::clob,'{32,535,5645645,6767,76,67,56,48,979,978,7}');
    end loop;
end;
/
insert into t_ustore_function_covarpop_0004 select * from t_ustore_function_covarpop_0004;
insert into t_ustore_function_covarpop_0004 select * from t_ustore_function_covarpop_0004;

--step4: covar_pop函数在case when语句中使用；expect:成功
select case when covar_pop(case when col_1>10 then col_1 else col_4 end,col_6)>149 then covar_pop(case when col_2>10 then col_4 else col_1 end,col_6) else variance(case when col_4>50 then col_1 else col_6 end) end from t_ustore_function_covarpop_0004 order by 1;

--step5: 清理表；expect:成功
drop table t_ustore_function_covarpop_0004;
drop sequence if exists s_ustore_function_covarpop_0004;